package gmail.pyanoveugen.studyproject;

import gmail.pyanoveugen.studyproject.bean.UserBean;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class JDBC_VerySimpleExample {

    public static void main(String[] args) throws Exception {

        // try to load Drive class which will be provide sending SQL queries to DB. In my case it's HSQLDB.
        try {
            Class.forName("org.hsqldb.jdbcDriver");
        } catch (ClassNotFoundException e) {
            System.out.println("ERROR: failed to load HSQLDB JDBC driver. Check you classpath.");
            throw e;
        }

        // try to connection to DB. In this case you database should be started.
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/", "sa", "");
        } catch (SQLException e) {
            System.out.println("ERROR: DB connection fail. Check you instance (is it started) and connection parameters.");
            throw e;
        }

        // try to execute sql query
        String sql = "SELECT u.id, u.login, u.password FROM users u WHERE u.login = ?";
        //create PreparedStatement which will be managed SQL parsing and execution.
        PreparedStatement statement = connection.prepareStatement(sql);//this way cashed our query and if we call this query more then one time, this manager will skip parsing and will use query from cash.
                                                                       // In next line, we insert data in "?" symbol.

                                                                       //Also you can use other type statement.  From example: connection.createStatement().execute("SELECT * from user")
                                                                       // where you don't need put parameters in SQL query.
        statement.setString(1, "jeka");

        ResultSet resultSet = statement.executeQuery(); // please also read for
                                                        //        statement.execute();
                                                        //        statement.executeUpdate()
                                                        // is what case should use this methods.

        List<UserBean> userList = new LinkedList<UserBean>();

        //parse result set. in this place we parse all rows if it exists.
        while (resultSet.next()) {
            UserBean user = new UserBean();

            //parse result set. in this place we parse each rows.
            user.setId(resultSet.getLong("id")); //you can fetch results by two way. you can access by position or by column name.
            user.setLogin(resultSet.getString(2));
            user.setPassword(resultSet.getString("password"));

            // collect all result to list for more comfortable usages.
            userList.add(user);
        }

        // after parsing don't forget for closing and realise all resources.
        if (resultSet.isClosed() == false)
            resultSet.close();
        if (statement.isClosed() == false)
            statement.close();

        // and close connection if it need.
        if (connection.isClosed() == false)
            connection.close();

        //now we can work with result, which we loaded form database.
        for (UserBean userBean : userList) {
            System.out.println(userBean);
        }

        // I hope, it's helped you.
    }
}
